/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package modele;

/**
 *
 * @author abach
 */
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.TreeSet;

public class DAO {

    public static Collection<Dep> getLesDeps() {
        Collection<Dep> lesDeps = new TreeSet<Dep>();

        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery("select distinct departement "
                    + "from medecin");

            while (rs.next()) { // va chercher ligne par ligne
                String departement = rs.getString("departement"); // pour departement, mets ds var departement
                lesDeps.add(new Dep(departement)); // le mets dans une collection lesDeps
            }
        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);

        }
        return lesDeps;  // retourne cette collection
    }

    public static Collection<Spe> getLesSpes() {
        Collection<Spe> lesSpe = new TreeSet<Spe>();
        try {
            Connection con = Connect.get();
            Statement req3;
            req3 = con.createStatement();
            ResultSet rs3 = req3.executeQuery("select distinct specialitecomplementaire "
                    + "from medecin where specialitecomplementaire is not null;");

            while (rs3.next()) { // va chercher ligne par ligne
                String specialite = rs3.getString("specialitecomplementaire");  // pour sep, mets ds var specialite
                lesSpe.add(new Spe(specialite)); // le mets dans une collection lesSpe
            }

        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);

        }
        return lesSpe; // retourne cett collection
    }

    static Collection<Med> getLesMeds() {
        Collection<Med> lesMeds = new TreeSet<Med>();
        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery("select *  "
                    + "from medecin");

            while (rs.next()) {

                String nom = rs.getString("nom");
                String prenom = rs.getString("prenom");
                String adresse = rs.getString("adresse");
                String tel = rs.getString("tel");
                String spec = rs.getString("specialitecomplementaire");
                String id = rs.getString("id");
                String dep = rs.getString("departement");

                lesMeds.add(new Med(nom, prenom, adresse, tel, spec, id, dep));
            }

        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);

        }
        return lesMeds;
    }
}